﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DA
{  
    public class danhsachhocsinh_DA : ConnectData
    {
        DataLINQHocSinhDataContext db = new DataLINQHocSinhDataContext();
        HOCSINH hs = new HOCSINH();
        public IQueryable<HOCSINH> load_hocsinh()
        {
            var query = from hocsinh in db.HOCSINHs
                        select hocsinh;
            IQueryable<HOCSINH> hocs = query;
            return hocs;
        }
        public object laytheoml(string ml)
        {
            return db.HOCSINHs.Where(p => p.MALOP == ml).ToList();
        }
        public DataTable LoadDSDHSTHEOHK()
        { 
            string sql=" CREATE TABLE Result ( STT int, MAHS nvarchar(20),TENHS nvarchar(70), TENLOP nvarchar(100) , DiemTB_HK1 float, DiemTB_HK2 float) "+
                        "INSERT INTO Result (STT, MAHS,TENHS, TENLOP, DiemTB_HK1) "+
                        "select  ROW_NUMBER() OVER(ORDER BY b.MAHS) STT, b.MAHS, TENHS, TENLOP, SUM(DTB)/m.TongMon DiemTB_HK1 "+
                        "from BANGDIEM b "+
                        "left join "+
                        "(select MAHS, MAHK, COUNT(MAMH) TongMon "+
                        "from BANGDIEM where MAHK = 'HK1' and ISNULL(DTB,0) > 0 "+
                        "group by MAHS, MaHK) m on b.MAHS = m.MAHS and b.MAHK = m.MAHK "+ 
                         "left join LOP on b.MALOP = LOP.MALOP "+
                         "left join HOCSINH on b.MAHS = HOCSINH.MAHS "+
                         "where b.MAHK = 'HK1' group by b.MAHS,TENHS, TENLOP, m.TongMon "+

                         "update Result SET DiemTB_HK2 = k.DiemTB_HK2 from Result, "+
	                         "(select b.MAHS, SUM(DTB)/m.TongMon DiemTB_HK2 "+
		                        "from BANGDIEM b "+
                                "left join "+
	                        "(select MAHS, MAHK, COUNT(MAMH) TongMon "+ 
		                        "from BANGDIEM where MAHK = 'HK2' and ISNULL(DTB,0) > 0 group by MAHS, MaHK "+
	                        ") m on b.MAHS = m.MAHS and b.MAHK = m.MAHK "+
                        "where b.MAHK = 'HK2' group by b.MAHS, m.TongMon "+
                         ") k where Result.MAHS = k.MAHS "+
                         "select * from Result "+
                         "drop table Result " ;
            return LayDatatable(sql);
        }
        
    }
}
